USE [msdb]
GO

IF NOT EXISTS(SELECT * FROM dbo.sysjobs WHERE name = N'Database Maintenance')
BEGIN
	BEGIN TRANSACTION
	DECLARE @ReturnCode int, @jobId binary(16)

	EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Database Maintenance', @enabled = 1, @notify_level_eventlog = 0, @notify_level_email = 0, @notify_level_netsend = 0,
		@notify_level_page = 0, @delete_level = 0, @job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'Execute', @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1,
		@on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL',
		@command=N'DECLARE @Database nvarchar(128), @SQL nvarchar(max);
-- Create transaction log backups (allow propagation to all secondaries)
DECLARE cDatabases CURSOR FAST_FORWARD FOR SELECT d.name FROM sys.databases d JOIN sys.availability_databases_cluster adc ON adc.group_database_id = d.group_database_id AND adc.database_name = d.name JOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = adc.group_id JOIN sys.availability_replicas ar ON ar.replica_id = d.replica_id AND ar.group_id = adc.group_id WHERE d.owner_sid <> 1 AND d.recovery_model_desc = ''FULL'' AND ags.primary_replica = ar.replica_server_name ORDER BY d.Name;
OPEN cDatabases;
FETCH NEXT FROM cDatabases INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = N''BACKUP LOG ['' + @Database + ''] TO DISK = N''''\\'' + @@SERVERNAME + ''\Backup\'' + @Database + ''.bak'''' WITH INIT;'';
	EXEC (@SQL);
	FETCH NEXT FROM cDatabases INTO @Database;
END
CLOSE cDatabases;
DEALLOCATE cDatabases;
-- Shirnk all databases
DECLARE cDatabases CURSOR FAST_FORWARD FOR SELECT d.name FROM sys.databases d JOIN sys.availability_databases_cluster adc ON adc.group_database_id = d.group_database_id AND adc.database_name = d.name JOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = adc.group_id JOIN sys.availability_replicas ar ON ar.replica_id = d.replica_id AND ar.group_id = adc.group_id WHERE d.owner_sid <> 1 AND d.recovery_model_desc = ''FULL'' AND ags.primary_replica = ar.replica_server_name ORDER BY d.Name;
OPEN cDatabases;
FETCH NEXT FROM cDatabases INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = N''USE ['' + @Database + ''];''
	EXEC (@SQL);
	SET @SQL = N''DBCC SHRINKDATABASE(N'''''' + @Database + '''''');''
	EXEC (@SQL);
	FETCH NEXT FROM cDatabases INTO @Database;
END
CLOSE cDatabases;
DEALLOCATE cDatabases;',
		@database_name = N'master', @flags = 0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Database Maintenance Schedule', @enabled = 1, @freq_type = 4, @freq_interval = 1,
		@freq_subday_type = 4, @freq_subday_interval = 60, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20150101, @active_end_date = 99991231,
		@active_start_time = 0, @active_end_time = 235959, @schedule_uid = N'afdb18ef-21bc-435e-82a4-d46668476a28'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

	COMMIT TRANSACTION
	GOTO EndSave

	QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

	EndSave:
END
GO